package com.study;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;

/**
 * @author huang
 */
@RestController
public class Controller {

    @GetMapping("/test")
    public void test(HttpServletResponse response) {

        /*
         * xls 文件, 07 版 office 之前
         */
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建工作表, 并指定名称
        HSSFSheet sheet = wb.createSheet();
        wb.setSheetName(0, "员工信息表");

        // 第一行
        HSSFRow row1 = sheet.createRow(0);

        /*
         * 跨行合并, 占一列两行
         */
        HSSFCell cell0 = row1.createCell(0);
        cell0.setCellValue("ID");
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        /*
         * 跨列合并, 占一行, 4列
         */
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellValue("商品数量");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 4));

        /*
         * 跨列合并, 占一行, 3列
         */
        HSSFCell cell2 = row1.createCell(5);
        cell2.setCellValue("员工销量");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 7));


        // 第二行
        HSSFRow row2 = sheet.createRow(1);
        // 第二行从 1 开始, 因为第一格占了两行
        HSSFCell cell3 = row2.createCell(1);
        cell3.setCellValue("Iphone");
        HSSFCell cell4 = row2.createCell(2);
        cell4.setCellValue("HUAWEI");
        HSSFCell cell5 = row2.createCell(3);
        cell5.setCellValue("VIVO");
        HSSFCell cell6 = row2.createCell(4);
        cell6.setCellValue("OPPO");

        HSSFCell cell7 = row2.createCell(5);
        cell7.setCellValue("张三");
        HSSFCell cell8 = row2.createCell(6);
        cell8.setCellValue("李四");
        HSSFCell cell9 = row2.createCell(7);
        cell9.setCellValue("王五");

        /*
         * 样式
         */
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 背景色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 字体
        HSSFFont font = wb.createFont();
        font.setBold(true);
        font.setFontName("黑体");
        font.setFontHeight((short) 16);
        cellStyle.setFont(font);
        // 全局设置高宽, 宽 20 个字符, 高 30px
        sheet.setDefaultColumnWidth(20 * 256);
        sheet.setDefaultRowHeightInPoints(30);
        // 第一列宽 40 个字符
        sheet.setColumnWidth(0, 40 * 256);
        // 当前行高 60px
        row1.setHeightInPoints(60);
        // 自动换行
        cellStyle.setWrapText(true);
        // 单元格插入换行符, 拼接字符串 "\r\n"


        // 下载
        ServletOutputStream outputStream;
        try {
            outputStream = response.getOutputStream();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", String.format("application/vnd.ms-excel;filename=%s", URLEncoder.encode("文件.xls", "UTF-8")));
            response.setHeader("Content-Disposition", String.format("attachment;filename=%s", URLEncoder.encode("文件.xls", "UTF-8")));
            wb.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
